
import datetime,time
from time import sleep
import psutil
import sys,os
import pyautogui
import sqlite3
import warnings
warnings.simplefilter("ignore", UserWarning)
sys.coinit_flags = 2
from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.keys import  Keys
from selenium.webdriver.support.expected_conditions import alert_is_present
from selenium.webdriver.support.select import Select
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities
DesiredCapabilities.INTERNETEXPLORER["ignoreProtectedModeSettings"] = True

from PyQt5.QtCore import QThread,QMutex,pyqtSignal

from bs4 import BeautifulSoup
from crypt import *
from charts import *

spider_mute = QMutex()

class Spider_Thread(QThread):

    spider_signal = pyqtSignal(int)

    def __init__(self):
        super(Spider_Thread,self).__init__()

    def run(self):
        spider_mute.lock()
        try:
            spider = Spider()
            rslt = spider.run()

            if rslt:
                self.spider_signal.emit(1)
            else:
                self.spider_signal.emit(0)
        except:
            self.spider_signal.emit(0)
        spider_mute.unlock()

class Spider():

    def __init__(self):
        super(Spider,self).__init__()
        self.con  = sqlite3.connect('local.db')
        self.cur = self.con.cursor()
        self.distinct_data = []

    def run(self):
        self.cur.execute('select username,passwd from vpn')
        self.tmp = self.cur.fetchone()
        try:
            self.username = decrypt(self.tmp[0])
            self.passwd_tmp = decrypt(self.tmp[1])
        except:
            self.username = None
            self.passwd_tmp = None
        while True:
            try:
                self.vpn = Vpn(self.passwd_tmp)
                break
            except:
                sleep(3)

        self.browser = webdriver.Ie()
        self.browser.set_page_load_timeout(30)
        self.wait = WebDriverWait(self.browser,15)

        if not self.passwd_tmp:
            self.close()
            return
        try:
            self.login()
            self.distinct_spider()
            return 1
        except:
            return 0

    def login(self):
        i = 1
        while i:
            try:
                print("第{}次登录中".format(i))
                self.browser.get("https://10.249.1.170:8881/report/login.do")
                passwd = self.wait.until(
                        EC.presence_of_element_located((By.ID,'UserPwd'))
                    )
                passwd.send_keys(self.passwd_tmp)
                signin = self.wait.until(
                        EC.presence_of_element_located((By.ID,'bstn2'))
                    )
                signin.click()
                try:
                    user_choose = self.wait.until(
                        EC.presence_of_element_located((By.ID,'duty'))
                        )
                    Select(user_choose).select_by_value(self.username)
                    signin.click()
                    return 1
                except:
                    pass
                i = 0
            except:
                sleep(1)
                i += 1
                if i == 5:
                    return 0

    def switch_to_content(self):
        self.browser.get("https://10.249.1.170:8881/report/login.do")
        times = 1
        while times:
            try:
                print('第{}次打开列表'.format(times))
                tmp = self.wait.until(
                            EC.presence_of_element_located((By.XPATH,"//ul[@id='leftMenuTree']/li/ul/li[2]/div/span[4]"))
                        )
                sleep(1)
                tmp.click()
                times = 0
                return 1
            except:
                self.login()
                print('dierci')
                # self.browser.get("https://10.249.1.170:8881/report/login.do")
                times += 1
                if times == 5:
                    break
                    return 0

    def close(self):
        pass

    def distinct(self,date):
        year = date.year
        month = date.month
        day = date.day
        times = 1
        while times:
            try:
                self.switch_to_content()
                print('第{}次地区报表'.format(times))
                tongji = self.wait.until(
                    EC.presence_of_element_located((By.LINK_TEXT,"分地区实时统计"))
                    )
                print('click',times)
                tongji.click()

                b = self.wait.until(
                        EC.presence_of_element_located((By.XPATH,"//iframe[@name='centerIframe']"))
                        )

                self.browser.switch_to_frame(b)
                times = 0
            except:
                times += 1
                if times == 10:
                    break
                    return None
                pass

        case_type_chk = self.wait.until(
                    EC.presence_of_all_elements_located((By.XPATH,'//input[@name="caseType_chk"]'))
            )
        for i in range(2,6):
            case_type_chk[i].click()

        year_option = self.wait.until(
                EC.presence_of_element_located((By.XPATH,"//select[@name='yearBegin']"))
            )
        Select(year_option).select_by_value(str(year))

        month_option = self.wait.until(
                    EC.presence_of_element_located((By.XPATH,'//select[@name="monthBegin"]'))
            )
        Select(month_option).select_by_value(str(month))
        day_begin_option = self.wait.until(
                    EC.presence_of_element_located((By.XPATH,'//select[@name="dateBegin"]'))
            )
        Select(day_begin_option).select_by_value(str(day))

        month_end_option = self.wait.until(
                    EC.presence_of_element_located((By.XPATH,'//select[@name="monthEnd"]'))
            )
        Select(month_end_option).select_by_value(str(month))
        day_en_option = self.wait.until(
                    EC.presence_of_element_located((By.XPATH,'//select[@name="dateEnd"]'))
            )
        Select(day_en_option).select_by_value(str(day))

        self.wait.until(
                    EC.presence_of_element_located((By.XPATH,'//input[@type="submit"]'))
            ).click()

        table = self.wait.until(
            EC.presence_of_element_located((By.XPATH,'//iframe[@name="dataFrame"]'))
        )
        self.browser.switch_to_frame(table)

        rslt = []
        soup = BeautifulSoup(self.browser.page_source,'lxml')
        for tr  in soup.find_all('tr'):
            tds = tr.find_all('td')
            for td in tds:
                rslt.append(td.text)

        return rslt[:-2]

    def data_in(self,date,data):
        date_int = int(datetime.datetime(date.year,date.month,date.day,12,0,0,0).timestamp())
        if data:
            len_data = len(data)
            range_disease =[]
            for i in range(120):
                if data[i] == '疾病类型':
                    range_disease.append(i)
            disease_count = range_disease[1] - range_disease[0]-4

            disease = data[6:disease_count+6]
            head_count = 11 + 5*disease_count
            disease_count_value = 2*disease_count+1

            #
            #
            dic = {
                    '汤口镇':"淮北市",
                    "谭家桥镇":"亳州市",
                    "三口镇":"宿州市",
                    "仙源镇":"阜阳市",
                    "新明乡":"蚌埠市",
                    "龙门乡":"淮南市",
                    "耿城镇":"滁州市",
                    "甘棠镇":"合肥市",
                    "焦村镇":"六安市",
                    "乌石镇":"马鞍山市",
                    "太平湖镇":"芜湖市",
                    "永丰乡":"铜陵市",
                    "新华乡":"池州市",
                    "新丰乡":"安庆市",
                    "不详乡镇":"宣城市"
                }
            #
            #
            city = None
            for i in range(head_count,len_data):
                order =(i-head_count)%disease_count_value
                if not order:
                    city = dic[data[i].replace('\u3000','')]
                    # city = data[i].replace('\u3000','')
                    continue
                else:
                    order2 = int((order-1)/2)
                    if data[i] != '-' and order%2 and order != 1:
                        disease2 = disease[order2].replace(' ','')
                        dup_sql = '''select * from disease where date = {} and city = "{}" and disease = "{}"
                                    '''.format(date_int,city,disease2)
                        self.cur.execute(dup_sql)
                        dup = self.cur.fetchall()
                        if dup:
                            sql = '''update disease set count = {} where date = {} and city = "{}" and disease = "{}" '''
                            sql2 = sql.format(int(data[i]),date_int,city,disease2)
                        else:
                            sql = '''insert into disease values ({},"{}","{}",{})'''
                            sql2 = sql.format(date_int,city,disease2,int(data[i]))
                        print(sql2)
                        self.cur.execute(sql2)
            self.con.commit()

    def distinct_spider(self):
        today = datetime.datetime.today().replace(hour=12)
        self.cur.execute('select max(date) from disease')
        max_date = self.cur.fetchone()[0]
        if max_date:
            max_date2 = datetime.datetime.fromtimestamp(max_date)
            gap = (today - max_date2).days
        else:
            gap = 30
        if not gap:
            gap = 1
        for i in range(gap):
            begin_date = today - datetime.timedelta(i)
            rslt = self.distinct(begin_date)
            if rslt:
                self.data_in(begin_date,rslt)
        self.browser.quit()
        self.vpn.close_vpn()


class Vpn():

    def __init__(self,passwd):
        super(Vpn, self).__init__()
        self.passwd = passwd

        while True:
            try:
                # self.start_vpn(self.passwd)
                self.vpn_status(self.passwd)
                break
            except ElementNotEnabled:
                sleep(3)



    def start_vpn(self,passwd):
        self.close_vpn()
        sleep(3)
        try:
            os.startfile("C:\Program Files (x86)\Sangfor\SSL\SangforCSClient\SangforCSClient.exe")
        except:
            os.startfile("C:\Program Files\Sangfor\SSL\SangforCSClient\SangforCSClient.exe")
        sleep(5)
        pyautogui.typewrite(passwd)
        pyautogui.hotkey('enter')

    def vpn_status(self,passwd):
        tmp = self.is_vpn_on()
        if tmp:
            pass
        else:
            self.start_vpn(passwd)

    def is_vpn_on(self):
        pids = psutil.pids()
        pids_list = []
        for i in pids:
            try:
                pid = psutil.Process(i)
                pids_list.append(pid.name())
            except:
                pass

        if "SangforCSClient.exe" in pids_list:
            return 1
        else:
            return 0

    def close_vpn(self):
        pids = psutil.pids()
        for i in pids:
            try:
                pid = psutil.Process(i).name()
                if pid in ["SangforCSClient.exe",'EasyConnect.exe']:
                    psutil.Process(i).terminate()
            except:
                pass

if __name__ == "__main__":
    spider = Spider()
    spider.run()
